package persistence.dao;

import java.sql.*;
import java.util.HashSet;
import java.util.Set;

import core.ModelObject;

import persistence.TransactionManager;
import persistence.dto.StorageUnitDTO;

public class StorageUnitsDBDAO extends ModelObject implements IStorageUnitsDAO
{
	/**
	 * 
	 */
	private static final long serialVersionUID = 3236989962664960352L;

	private int ERROR = -5;
	
	private static StorageUnitsDBDAO instance;
	
	private Connection connection;
	
	public static StorageUnitsDBDAO getInstance()
	{
		if(instance == null)
		{
			instance = new StorageUnitsDBDAO();
		}
		return instance;
	}
	
	private StorageUnitsDBDAO()
	{
		
	}
	
	@Override
	public Set<StorageUnitDTO> getAllStorageUnits()
	{
		connection = TransactionManager.getConnection();
		PreparedStatement stmt = null;
		ResultSet keyRS = null;
		Set<StorageUnitDTO> DTOset = new HashSet<StorageUnitDTO>();
		try
		{
			String sql = "select * from ProductContainers where containerType = ?";
			stmt = connection.prepareStatement(sql);
			stmt.setString(1, "storage unit");
			keyRS = stmt.executeQuery();
			int id;
			String name;
			
			while(keyRS.next())
			{
				id = keyRS.getInt(1);
				name = keyRS.getString(2);
				
				DTOset.add(new StorageUnitDTO(id, name));
			}
		}
		catch(SQLException e)
		{
			//Error
			System.out.println("Error in database get all Storage Units");
			return new HashSet<StorageUnitDTO>();
		}
		finally
		{
			try{
			if(stmt != null) stmt.close();
			if(keyRS != null) keyRS.close();
			}catch(Exception e){
				//Error
				return new HashSet<StorageUnitDTO>();
			}
		}
		return DTOset;
	}

	@Override
	public int add(StorageUnitDTO storageUnitDTO)
	{
		if(storageUnitDTO.getId() != 0)
			return storageUnitDTO.getId();
		connection = TransactionManager.getConnection();
		PreparedStatement stmt = null;
		Statement keyStmt = null;
		ResultSet keyRS = null;
		try
		{
			String sql = "insert into ProductContainers (name, containerType, parentID) "+
					"values (?,?,?)";
			stmt = connection.prepareStatement(sql);
			stmt.setString(1, storageUnitDTO.getName());
			stmt.setString(2, storageUnitDTO.getType());
			stmt.setInt(3, storageUnitDTO.getParentID());
			
			if(stmt.executeUpdate() == 1)
			{
				keyStmt = connection.createStatement();
				keyRS = keyStmt.executeQuery("select last_insert_rowid()");
				keyRS.next();
				int id = keyRS.getInt(1);
				return id;
			}
			else
			{
				//Error
				return ERROR;
			}
		}
		catch (SQLException e)
		{
			//Error
			return ERROR;
		}
		finally
		{
			try{
			if(stmt != null) stmt.close();
			if(keyRS != null) keyRS.close();
			if(keyStmt != null) keyStmt.close();
			}catch(Exception e){
				//Error
				return ERROR;
			}
		}
	}

	@Override
	public boolean edit(StorageUnitDTO storageUnitDTO)
	{
		// Is there more that needs to be updated in other tables?
		connection = TransactionManager.getConnection();
		PreparedStatement stmt = null;
		
		try
		{
			String sql = "UPDATE ProductContainers SET name = \""+storageUnitDTO.getName() +
						 "\", containerType = \""+storageUnitDTO.getType() +
						 "\", parentID = "+storageUnitDTO.getParentID() +
						 " WHERE id = "+storageUnitDTO.getId();
			stmt = connection.prepareStatement(sql);
			
			stmt.executeUpdate();
		}
		catch (SQLException e)
		{
			e.printStackTrace();
			return false;
		}
		return true;
	}

	@Override
	public boolean remove(StorageUnitDTO storageUnitDTO)
	{
		connection = TransactionManager.getConnection();
		PreparedStatement stmt = null;
		Statement keyStmt = null;
		ResultSet keyRS = null;
		try
		{
			String sql = "delete from ProductContainers where id = " + storageUnitDTO.getId();
			stmt = connection.prepareStatement(sql);
			
			stmt.executeUpdate();
		}
		catch (SQLException e)
		{
			return false;
		}
		finally
		{
			try{
			if(stmt != null) stmt.close();
			if(keyRS != null) keyRS.close();
			if(keyStmt != null) keyStmt.close();
			}catch(Exception e){
				return false;
				//Error
			}
		}
		return true;
	}

}
